var express = require('express');
var router = express.Router();
var mysql = require('mysql');

//配置好数据库连接
function createConn(){
    var conn = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'stumanager'
    });
    conn.on('error', function () {
        conn.connect(err => {
            if (err) console.log('数据库连接失败！\n' + err.stack);
            else console.log('数据库连接成功！');
        });
    });
    return conn;
}

/* GET home page. */
router.get('/', function (req, res, next) {
    var conn=createConn();
    if(conn){
        var strSQL = 'select * from student'
        conn.query(strSQL, (err, result) => {
            if (err) {
                console.log('查询出错！');
                conn.end();
                res.send('查询出错！');
            } else {
                // console.log(result);
                // res.send(result);
                res.render('studentlist', {
                    title: '学生信息表',
                    data: result
                })
            }
        });
    }
    // if(conn.state === 'disconnected')
    //     conn.connect(err => {
    //         if (err) console.log('数据库连接失败！\n' + err.stack);
    //         else console.log('数据库连接成功！');
    //     });

    

});

router.post('/add', function (req, res) {
    if(conn.state === 'disconnected')
        conn.connect(err => {
            if (err) console.log('数据库连接失败！\n' + err.stack);
            else console.log('数据库连接成功！');
        });

    // console.log(req.body);
    var stu = {
        sNo: req.body.sNo,
        sName: req.body.sName,
        sSex: req.body.sSex,
        sBirthday: req.body.sBirthday,
        class: req.body.class
    };

    var strSQL = "insert into student (sNo, sName, sSex, sBirthday, class) values ('"+stu.sNo+"','"+stu.sName+"','"+stu.sSex+"','"+stu.sBirthday+"','"+stu.class+"')";
    conn.query(strSQL, (err,result)=>{
        conn.end();
        if (err) {
            console.log('添加数据出错！');
            // conn.end();
            res.send({
                code: 1,
                msg:'添加数据出错'
            });
        } else {
            // console.log(result);
            // res.send(result);
            res.send({
                code: 0,
                msg: 'OK'
            });
        }
    });

});

router.post('/del',(req, res)=>{
    if(conn.state === 'disconnected')
        conn.connect(err => {
            if (err) console.log('数据库连接失败！\n' + err.stack);
            else console.log('数据库连接成功！');
        });

    var strSQL = "delete student where sNo = '"+req.body.sNo+"'"
    conn.query(strSQL, (err,result)=>{
        if (err) {
            console.log('删除数据出错！');
            // conn.end();
            res.send({
                code: 2,
                msg:'删除数据出错'
            });
        } else {
            // console.log(result);
            // res.send(result);
            res.send({
                code: 0,
                msg: 'OK'
            });
        }
    });

});



module.exports = router;
